package com.binarysoft.service.type;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.Collection;
import java.util.HashMap;
import java.util.Map;

import com.binarysoft.service.common.multiconnpool.MultipleConnectionPoolService;

import common.framework.dsb.client.DefaultServiceFactory;
import common.framework.dsb.client.ServiceFactory;

/**
 * @author David
 * 
 */
public class TypeMysqlDAO implements TypeDAO {

	@Override
	public Map<String, Type> loadAllTypes() throws Exception {
		ServiceFactory serviceFactory = new DefaultServiceFactory();
		MultipleConnectionPoolService connectionPoolService = serviceFactory.lookup("DSB/MultipleConnectionPoolService");
		String sql = "select * from types";
		Connection connection = connectionPoolService.getConnection(DSB_SERVER_POOL_NAME);
		Map<String, Type> result = new HashMap<String, Type>();
		try {
			Statement stmt = connection.createStatement();
			ResultSet rs = stmt.executeQuery(sql);
			while (rs.next()) {
				Type type = new Type();
				type.setID(rs.getString("type_id").toLowerCase());
				type.setName(rs.getString("type_name"));
				type.setDescription(rs.getString("type_desc"));
				String parentID = rs.getString("parent_id");
				if (parentID != null) {
					parentID = parentID.toLowerCase();
				}
				type.setParentID(parentID);
				result.put(type.getID(), type);
			}
			rs.close();
			stmt.close();
		} finally {
			if (connection != null) {
				connection.close();
			}
		}
		return result;
	}

	@Override
	public void addType(String typeID, String typeName, String typeDesc, String parentTypeID) throws Exception {
		ServiceFactory serviceFactory = new DefaultServiceFactory();
		MultipleConnectionPoolService connectionPoolService = serviceFactory.lookup("DSB/MultipleConnectionPoolService");
		String sql = "insert into types values (?, ?, ?,?)";
		Connection connection = connectionPoolService.getConnection(DSB_SERVER_POOL_NAME);
		try {
			PreparedStatement pstmt = connection.prepareStatement(sql);
			pstmt.setString(1, typeID);
			pstmt.setString(2, typeName);
			pstmt.setString(3, typeDesc);
			pstmt.setString(4, parentTypeID);
			pstmt.executeUpdate();
			pstmt.close();
		} finally {
			if (connection != null) {
				connection.close();
			}
		}
	}

	@Override
	public void deleteType(String typeID) throws Exception {
		ServiceFactory serviceFactory = new DefaultServiceFactory();
		MultipleConnectionPoolService connectionPoolService = serviceFactory.lookup("DSB/MultipleConnectionPoolService");
		String sql = "delete from types where type_id = ?";
		Connection connection = connectionPoolService.getConnection(DSB_SERVER_POOL_NAME);
		try {
			PreparedStatement pstmt = connection.prepareStatement(sql);
			pstmt.setString(1, typeID);
			pstmt.executeUpdate();
			pstmt.close();
		} finally {
			if (connection != null) {
				connection.close();
			}
		}

	}

	@Override
	public void deleteType(Collection<String> typeIDs) throws Exception {
		ServiceFactory serviceFactory = new DefaultServiceFactory();
		MultipleConnectionPoolService connectionPoolService = serviceFactory.lookup("DSB/MultipleConnectionPoolService");
		String sql = "delete from types where type_id = ?";
		Connection connection = connectionPoolService.getConnection(DSB_SERVER_POOL_NAME);
		try {
			PreparedStatement pstmt = connection.prepareStatement(sql);
			for (String typeID : typeIDs) {
				pstmt.setString(1, typeID);
				pstmt.addBatch();
			}
			pstmt.executeBatch();
			pstmt.close();
		} finally {
			if (connection != null) {
				connection.close();
			}
		}
	}

	@Override
	public void updateType(String typeID, String typeName, String typeDesc, String parentTypeID) throws Exception {
		ServiceFactory serviceFactory = new DefaultServiceFactory();
		MultipleConnectionPoolService connectionPoolService = serviceFactory.lookup("DSB/MultipleConnectionPoolService");
		String sql = "update types set type_name = ?, type_desc = ?, parent_id = ? where type_id = ?";
		Connection connection = connectionPoolService.getConnection(DSB_SERVER_POOL_NAME);
		try {
			PreparedStatement pstmt = connection.prepareStatement(sql);
			pstmt.setString(1, typeName);
			pstmt.setString(2, typeDesc);
			pstmt.setString(3, parentTypeID);
			pstmt.setString(4, typeID);
			pstmt.executeUpdate();
			pstmt.close();
		} finally {
			if (connection != null) {
				connection.close();
			}
		}
	}
}
